{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "importing Jupyter notebook from myPooledDB.ipynb\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "\"\\nprint(myPooledDB.POOL)\\na=myPooledDB()\\nprint(a.POOL)\\nb=myPooledDB()\\nprint(b.POOL)\\nprint(myPooledDB.POOL)\\n\\na.setPOOL()\\nprint('\\nafter')\\nprint(a.POOL)\\nprint(b.POOL)\\nprint(myPooledDB.POOL)\\nif(a.POOL==b.POOL):\\n    print('yes')\\n\\nprint('\\nset from instance')\\na.POOL=1;\\nprint(a.POOL)\\nprint(b.POOL)\\nprint(myPooledDB.POOL)\\nprint('set from class')\\nmyPooledDB.POOL=1\\nprint(a.POOL)\\nprint(b.POOL)\\nprint(myPooledDB.POOL)\\n\\nprint('\\nreset')\\nb.setPOOL()\\nprint(a.POOL)\\nprint(b.POOL)\\nprint(myPooledDB.POOL)\\n\""
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pymysql\n",
    "import datetime\n",
    "import Ipynb_importer\n",
    "from myItem import myItem\n",
    "from myPooledDB import myPooledDB\n",
    "import traceback\n",
    "\n",
    "class myPooledDBFuns():\n",
    "    def __init__(self):\n",
    "        return None\n",
    "    \n",
    "    \n",
    "    #创建连接\n",
    "    @staticmethod\n",
    "    def create_conn():\n",
    "        try:\n",
    "            conn = myPooledDB.POOL.connection()\n",
    "            cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)\n",
    "            return conn,cursor\n",
    "        except:\n",
    "            print(\"创建连接出错\")\n",
    "            return 0;\n",
    "    \n",
    "    \n",
    "    \n",
    "    # 插入一条数据\n",
    "    @staticmethod\n",
    "    def insert(website,insertList):\n",
    "        result=0\n",
    "        conn,cursor = myPooledDBFuns.create_conn()\n",
    "        sql=\"INSERT INTO {0}(pname, price, productURL, stock, proPic, description, updateTime) VALUES (%s, %s, %s, %s, %s, %s, %s)\".format(website)\n",
    "        for j in range(len(insertList)):\n",
    "            item=insertList[j]\n",
    "            values = (item.getPname(), item.getPrice(), item.getProductURL(), item.getStock(), item.getProPic(), item.getDescription(), item.getUpdateTime())\n",
    "            try:\n",
    "                result = cursor.execute(sql,values)\n",
    "                conn.commit()\n",
    "            except:\n",
    "                print(\"插入数据出错\")\n",
    "                conn.rollback()  # 发生错误时回滚\n",
    "                break\n",
    "        print(\"插入数据成功\")\n",
    "        myPooledDBFuns.close_conn(conn,cursor)\n",
    "        return result\n",
    "    \n",
    "    \n",
    "    def delete(website,deleteList):\n",
    "        result=0\n",
    "        conn,cursor = myPooledDBFuns.create_conn()\n",
    "        for j in range(len(deleteList)):\n",
    "            item=deleteList[j]\n",
    "            sql=\"DELETE FROM {0} WHERE pid = %s\".format(website) % (item.getPid())\n",
    "            try:\n",
    "                result = cursor.execute(sql)\n",
    "                conn.commit()\n",
    "            except:\n",
    "                print(\"删除数据出错\")\n",
    "                conn.rollback()  # 发生错误时回滚\n",
    "                break\n",
    "        print(\"删除数据成功\")\n",
    "        myPooledDBFuns.close_conn(conn,cursor)\n",
    "        return result\n",
    "        \n",
    "        \n",
    "    # 查询一条数据\n",
    "    @staticmethod\n",
    "    def fetch_one(sql,args):\n",
    "        result=0\n",
    "        conn,cursor = myPooledDBFuns.create_conn()\n",
    "        try:\n",
    "            cursor.execute(sql,args)\n",
    "            result = cursor.fetchone()\n",
    "        except:\n",
    "            print(\"查询数据出错\")\n",
    "            conn.rollback()  # 发生错误时回滚\n",
    "        print(\"查询数据成功\")\n",
    "        myPooledDBFuns.close_conn(conn,cursor)\n",
    "        return result\n",
    "    \n",
    "    \n",
    "    # 查询所有数据\n",
    "    @staticmethod\n",
    "    def fetch_all(website):\n",
    "        result=0\n",
    "        conn,cur = myPooledDBFuns.create_conn()\n",
    "        sql = \"SELECT * FROM {0}\".format(website)\n",
    "        try:\n",
    "            # 执行SQL语句\n",
    "            cur.execute(sql)\n",
    "            # 获取所有记录列表\n",
    "            oldProductList = cur.fetchall()  #返回值为列表，列表内每一元素类型为字典\n",
    "            #print(type(oldProductList))\n",
    "            #print(list(oldProductList[0]))\n",
    "            for i in range(len(oldProductList)):\n",
    "                oldProductList[i]=list(oldProductList[i].values())\n",
    "                oldProductList[i]=myItem(oldProductList[i][0],oldProductList[i][1],oldProductList[i][2],\\\n",
    "                                         oldProductList[i][3],oldProductList[i][4],oldProductList[i][5],\\\n",
    "                                         oldProductList[i][6],oldProductList[i][7])\n",
    "                #print(type(oldProductList[i]))\n",
    "            print('查找成功，当前数据库中共有'+str(len(oldProductList))+'商品')\n",
    "            result = oldProductList\n",
    "        except:\n",
    "            print(\"查询数据出错\")\n",
    "            error = traceback.format_exc()\n",
    "            print(error)\n",
    "            conn.rollback()  # 发生错误时回滚\n",
    "        myPooledDBFuns.close_conn(conn,cur)\n",
    "        return result\n",
    "        \n",
    "    \n",
    "    # 关闭连接\n",
    "    @staticmethod\n",
    "    def close_conn(conn,cursor):\n",
    "        try:\n",
    "            cursor.close()\n",
    "            conn.close()\n",
    "            return 1\n",
    "        except:\n",
    "            print(\"关闭连接出错\")\n",
    "            return 0\n",
    "    \n",
    "    \n",
    "    \n",
    "'''\n",
    "print(myPooledDB.POOL)\n",
    "a=myPooledDB()\n",
    "print(a.POOL)\n",
    "b=myPooledDB()\n",
    "print(b.POOL)\n",
    "print(myPooledDB.POOL)\n",
    "\n",
    "a.setPOOL()\n",
    "print('\\nafter')\n",
    "print(a.POOL)\n",
    "print(b.POOL)\n",
    "print(myPooledDB.POOL)\n",
    "if(a.POOL==b.POOL):\n",
    "    print('yes')\n",
    "\n",
    "print('\\nset from instance')\n",
    "a.POOL=1;\n",
    "print(a.POOL)\n",
    "print(b.POOL)\n",
    "print(myPooledDB.POOL)\n",
    "print('set from class')\n",
    "myPooledDB.POOL=1\n",
    "print(a.POOL)\n",
    "print(b.POOL)\n",
    "print(myPooledDB.POOL)\n",
    "\n",
    "print('\\nreset')\n",
    "b.setPOOL()\n",
    "print(a.POOL)\n",
    "print(b.POOL)\n",
    "print(myPooledDB.POOL)\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
